﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Sale
{
    public partial class 原料采购计划查看 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected string 年度;
        protected string 季度;
        protected string YouGuan;
        protected string YouGuanJiHuaZhiJinDanJia;
        protected string YouGuanJiHuaZhiJinJine;

        protected string TaoGuan;
        protected string TaoGuanJiHuaZhiJinDanJia;
        protected string TaoGuanJiHuaZhiJinJine;

        protected string YouGuanJieGu;
        protected string YouGuanJieGuJiHuaZhiJinDanJia;
        protected string YouGuanJieGuJiHuaZhiJinJine;

        protected string TaoGuanJieGu;
        protected string TaoGuanJieGuJiHuaZhiJinDanJia;
        protected string TaoGuanJieGuJiHuaZhiJinJine;

        protected string ChengPinJieGu;
        protected string ChengPinJieGuJiHuaZhiJinDanJia;
        protected string ChengPinJieGuJiHuaZhiJinJine;

        protected string YouGuan_N80;
        protected string YouGuanJiHuaZhiJinDanJia_N80;
        protected string YouGuanJiHuaZhiJinJine_N80;

        protected string TaoGuan_N80;
        protected string TaoGuanJiHuaZhiJinDanJia_N80;
        protected string TaoGuanJiHuaZhiJinJine_N80;

        protected string YouGuanJieGu_N80;
        protected string YouGuanJieGuJiHuaZhiJinDanJia_N80;
        protected string YouGuanJieGuJiHuaZhiJinJine_N80;

        protected string TaoGuanJieGu_N80;
        protected string TaoGuanJieGuJiHuaZhiJinDanJia_N80;
        protected string TaoGuanJieGuJiHuaZhiJinJine_N80;

        protected string ChengPinJieGu_N80;
        protected string ChengPinJieGuJiHuaZhiJinDanJia_N80;
        protected string ChengPinJieGuJiHuaZhiJinJine_N80;
        protected void Page_Load(object sender, EventArgs e)
        {
            年度=Request.QueryString["年度"];
            季度 = Request.QueryString["季度"];
            if (!IsPostBack)
            {
                getData();
            }
            if (季度 == "13")
            {
                季度 = "1";
            }
            if (季度 == "14")
            {
                季度 = "2";
            }
            if (季度 == "15")
            {
                季度 = "3";
            }
            if (季度 == "16")
            {
                季度 = "4";
            }
            
        }
        public void getData()
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter("select * from 原料采购计划 where 年度='" + 年度 + "' and 季度='" + Request.QueryString["季度"] + "'", Conn);
            sda.Fill(ds);
            YouGuan = ds.Tables[0].Rows[0][2].ToString();
            YouGuanJiHuaZhiJinDanJia = double.Parse(ds.Tables[0].Rows[0][3].ToString()).ToString("0.00");
            YouGuanJiHuaZhiJinJine = (double.Parse(ds.Tables[0].Rows[0][2].ToString()) * double.Parse(ds.Tables[0].Rows[0][3].ToString())).ToString("0.00");

            TaoGuan = ds.Tables[0].Rows[0][4].ToString();
            TaoGuanJiHuaZhiJinDanJia = double.Parse(ds.Tables[0].Rows[0][5].ToString()).ToString("0.00");
            TaoGuanJiHuaZhiJinJine = (double.Parse(ds.Tables[0].Rows[0][4].ToString()) * double.Parse(ds.Tables[0].Rows[0][5].ToString())).ToString("0.00");

            YouGuanJieGu = ds.Tables[0].Rows[0][6].ToString();
            YouGuanJieGuJiHuaZhiJinDanJia = double.Parse(ds.Tables[0].Rows[0][7].ToString()).ToString("0.00");
            YouGuanJieGuJiHuaZhiJinJine = (double.Parse(ds.Tables[0].Rows[0][6].ToString()) * double.Parse(ds.Tables[0].Rows[0][7].ToString())).ToString("0.00");

            TaoGuanJieGu = ds.Tables[0].Rows[0][8].ToString();
            TaoGuanJieGuJiHuaZhiJinDanJia = double.Parse(ds.Tables[0].Rows[0][9].ToString()).ToString("0.00");
            TaoGuanJieGuJiHuaZhiJinJine = (double.Parse(ds.Tables[0].Rows[0][8].ToString()) * double.Parse(ds.Tables[0].Rows[0][9].ToString())).ToString("0.00");

            ChengPinJieGu = ds.Tables[0].Rows[0][10].ToString();
            ChengPinJieGuJiHuaZhiJinDanJia = double.Parse(ds.Tables[0].Rows[0][11].ToString()).ToString("0.00");
            ChengPinJieGuJiHuaZhiJinJine = (double.Parse(ds.Tables[0].Rows[0][10].ToString()) * double.Parse(ds.Tables[0].Rows[0][11].ToString())).ToString("0.00");

            YouGuan_N80 = ds.Tables[0].Rows[0][12].ToString();
            YouGuanJiHuaZhiJinDanJia_N80 = double.Parse(ds.Tables[0].Rows[0][13].ToString()).ToString("0.00");
            YouGuanJiHuaZhiJinJine_N80 = (double.Parse(ds.Tables[0].Rows[0][12].ToString()) * double.Parse(ds.Tables[0].Rows[0][13].ToString())).ToString("0.00");

            TaoGuan_N80 = ds.Tables[0].Rows[0][14].ToString();
            TaoGuanJiHuaZhiJinDanJia_N80 = double.Parse(ds.Tables[0].Rows[0][15].ToString()).ToString("0.00");
            TaoGuanJiHuaZhiJinJine_N80 = (double.Parse(ds.Tables[0].Rows[0][14].ToString()) * double.Parse(ds.Tables[0].Rows[0][15].ToString())).ToString("0.00");

            YouGuanJieGu_N80 = ds.Tables[0].Rows[0][16].ToString();
            YouGuanJieGuJiHuaZhiJinDanJia_N80 = double.Parse(ds.Tables[0].Rows[0][17].ToString()).ToString("0.00");
            YouGuanJieGuJiHuaZhiJinJine_N80 = (double.Parse(ds.Tables[0].Rows[0][16].ToString()) * double.Parse(ds.Tables[0].Rows[0][17].ToString())).ToString("0.00");

            TaoGuanJieGu_N80 = ds.Tables[0].Rows[0][18].ToString();
            TaoGuanJieGuJiHuaZhiJinDanJia_N80 = double.Parse(ds.Tables[0].Rows[0][19].ToString()).ToString("0.00");
            TaoGuanJieGuJiHuaZhiJinJine_N80 = (double.Parse(ds.Tables[0].Rows[0][18].ToString()) * double.Parse(ds.Tables[0].Rows[0][19].ToString())).ToString("0.00");

            ChengPinJieGu_N80 = ds.Tables[0].Rows[0][20].ToString();
            ChengPinJieGuJiHuaZhiJinDanJia_N80 = double.Parse(ds.Tables[0].Rows[0][21].ToString()).ToString("0.00");
            ChengPinJieGuJiHuaZhiJinJine_N80 = (double.Parse(ds.Tables[0].Rows[0][20].ToString()) * double.Parse(ds.Tables[0].Rows[0][21].ToString())).ToString("0.00");

        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("模板/原料采购计划.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "原料采购计划";
            hssfworkbook.SummaryInformation = si;
        }
        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("原料采购计划") + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void Button_Export_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("原料采购计划");
            string title = "";
            if (季度 == "0")
            {
                title = 年度 + "年";
            }
            else
            {
                title = 年度 + "年" + 季度 + "季度";
            }
            sheet1.GetRow(0).GetCell(0).SetCellValue(title + "原料采购计划");
            string 时间 = DateTime.Now.ToString("yyyy-MM-dd");
            sheet1.GetRow(1).GetCell(2).SetCellValue("时间:" + 时间);
            getData();

            sheet1.GetRow(3).GetCell(5).SetCellValue(YouGuan);
            sheet1.GetRow(3).GetCell(7).SetCellValue(YouGuanJiHuaZhiJinDanJia);
            sheet1.GetRow(3).GetCell(9).SetCellValue(YouGuanJiHuaZhiJinJine);


            sheet1.GetRow(4).GetCell(5).SetCellValue(TaoGuan);
            sheet1.GetRow(4).GetCell(7).SetCellValue(TaoGuanJiHuaZhiJinDanJia);
            sheet1.GetRow(4).GetCell(9).SetCellValue(TaoGuanJiHuaZhiJinJine);

            sheet1.GetRow(5).GetCell(5).SetCellValue(YouGuanJieGu);
            sheet1.GetRow(5).GetCell(7).SetCellValue(YouGuanJieGuJiHuaZhiJinDanJia);
            sheet1.GetRow(5).GetCell(9).SetCellValue(YouGuanJieGuJiHuaZhiJinJine);

            sheet1.GetRow(6).GetCell(5).SetCellValue(TaoGuanJieGu);
            sheet1.GetRow(6).GetCell(7).SetCellValue(TaoGuanJieGuJiHuaZhiJinDanJia);
            sheet1.GetRow(6).GetCell(9).SetCellValue(TaoGuanJieGuJiHuaZhiJinJine);

            sheet1.GetRow(7).GetCell(5).SetCellValue(ChengPinJieGu);
            sheet1.GetRow(7).GetCell(7).SetCellValue(ChengPinJieGuJiHuaZhiJinDanJia);
            sheet1.GetRow(7).GetCell(9).SetCellValue(ChengPinJieGuJiHuaZhiJinJine);

            sheet1.GetRow(8).GetCell(5).SetCellValue(YouGuan_N80);
            sheet1.GetRow(8).GetCell(7).SetCellValue(YouGuanJiHuaZhiJinDanJia_N80);
            sheet1.GetRow(8).GetCell(9).SetCellValue(YouGuanJiHuaZhiJinJine_N80);

            sheet1.GetRow(9).GetCell(5).SetCellValue(TaoGuan_N80);
            sheet1.GetRow(9).GetCell(7).SetCellValue(TaoGuanJiHuaZhiJinDanJia_N80);
            sheet1.GetRow(9).GetCell(9).SetCellValue(TaoGuanJiHuaZhiJinJine_N80);

            sheet1.GetRow(10).GetCell(5).SetCellValue(YouGuanJieGu_N80);
            sheet1.GetRow(10).GetCell(7).SetCellValue(YouGuanJieGuJiHuaZhiJinDanJia_N80);
            sheet1.GetRow(10).GetCell(9).SetCellValue(YouGuanJieGuJiHuaZhiJinJine_N80);

            sheet1.GetRow(11).GetCell(5).SetCellValue(TaoGuanJieGu_N80);
            sheet1.GetRow(11).GetCell(7).SetCellValue(TaoGuanJieGuJiHuaZhiJinDanJia_N80);
            sheet1.GetRow(11).GetCell(9).SetCellValue(TaoGuanJieGuJiHuaZhiJinJine_N80);

            sheet1.GetRow(12).GetCell(5).SetCellValue(ChengPinJieGu_N80);
            sheet1.GetRow(12).GetCell(7).SetCellValue(ChengPinJieGuJiHuaZhiJinDanJia_N80);
            sheet1.GetRow(12).GetCell(9).SetCellValue(ChengPinJieGuJiHuaZhiJinJine_N80);       

            WriteToFile();
            Response.End();
        }
    }
}
